package com.hc.conferencecheck.utils;

import com.hc.conferencecheck.entity.entityDTO.ConventionerAttendRecord;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 用于导出Excel文件的工具类
 */
public class ExportExcelUtils {
    /**
     * 导出表格的表名
     */
    private String title;
    /**
     * 导出表格的列名
     */
    private List<String> rowName;
    /**
     * 对象数组的List集合
     */
    private List dataList = new ArrayList<>();

    private HttpServletResponse response;


    /**
     * 传入要导入的数据
     */
    public ExportExcelUtils(String title, List<String> rowName, List dataList, HttpServletResponse response) {
        this.title = title;
        this.rowName = rowName;
        this.dataList = dataList;
        this.response = response;
    }

    /**
     * 导出用户会议申请记录
     */
    public void exportConventionerAttendRecords() {
        try {
            // 创建一个excel对象
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(1000);
            // 创建表格
            Sheet sheet = sxssfWorkbook.createSheet(title);

            // 产生表格标题行
            // 行
            Row rowm = sheet.createRow(0);
            // 单元格
            Cell cellTiltle = rowm.createCell(0);

            // sheet样式定义
            // 头样式
            CellStyle columnTopStyle = this.getColumnTopStyle(sxssfWorkbook);
            // 单元格样式
            CellStyle style = this.getStyle(sxssfWorkbook);
            /**
             * 参数说明
             * 从0开始   第一行 第一列 都是从角标0开始
             * 行 列 行列    (0,0,0,5)  合并第一行 第一列  到第一行 第六列
             * 起始行，起始列，结束行，结束列
             *
             * new Region()  这个方法使过时的
             */
            // 合并第一行的所有列
            sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (rowName.size() - 1)));
            //设置单元格样式
            cellTiltle.setCellStyle(columnTopStyle);
            //设置单元格的值
            cellTiltle.setCellValue(title);

            // 表格列的列数=列名数组的长度
            int columnNum = rowName.size();
            // 在第二行创建行
            Row rowRowName = sheet.createRow(1);
            CellStyle cells = sxssfWorkbook.createCellStyle();
            cells.setBottomBorderColor(HSSFColor.BLACK.index);
            rowRowName.setRowStyle(cells);

            // 循环 将列名放进去
            for (int i = 0; i < columnNum; i++) {

                Cell cellRowName = rowRowName.createCell((int) i);
                // 单元格类型
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
                // 得到列的值
                String s = rowName.get(i);
                HSSFRichTextString text = new HSSFRichTextString(s);
                // 设置列的值
                cellRowName.setCellValue(text);
                // 样式
                cellRowName.setCellStyle(columnTopStyle);

                //  让列宽随着导出的列长自动适应
                sheet.setColumnWidth(i, s.getBytes().length * 2 * 256);
            }

            // 将查询到的数据设置到对应的单元格中
            int listSize = dataList.size();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            for (int i = 0; i < listSize; i++) {
                //遍历每个对象
                ConventionerAttendRecord conventionerAttendRecord = (ConventionerAttendRecord) dataList.get(i);
                //创建所需的行数，+2是因为标题和表头已经占了2行
                Row row = sheet.createRow(i + 2);

                // 第一列设置为序号
                Cell cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(i + 1);

                //设置单元行每列的值
                //会议申请记录ID
                Cell cell2 = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
                cell2.setCellValue(conventionerAttendRecord.getRecordId());

                //会议ID
                Cell cell3 = row.createCell(2, HSSFCell.CELL_TYPE_NUMERIC);
                cell3.setCellValue(conventionerAttendRecord.getConventionerId());

                //会议名字
                Cell cell4 = row.createCell(3, HSSFCell.CELL_TYPE_NUMERIC);
                cell4.setCellValue(conventionerAttendRecord.getConferenceName());

                //用户ID
                Cell cell5 = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
                cell5.setCellValue(conventionerAttendRecord.getConventionerId());

                //用户名字
                Cell cell6 = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
                cell6.setCellValue(conventionerAttendRecord.getName());


                //用户手机号
                Cell cell7 = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
                cell7.setCellValue(conventionerAttendRecord.getPhoneNumber());

                //申请时间
                Cell cell8 = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
                //转换时间
                Date applyTime = conventionerAttendRecord.getApplyTime();
                String applyTimeStr = "";
                if (applyTime == null) {
                    cell8.setCellValue("");
                } else {
                    applyTimeStr = sdf.format(applyTime);
                }
                cell8.setCellValue(applyTimeStr);

                //是否签到
                Cell cell9 = row.createCell(8, HSSFCell.CELL_TYPE_STRING);
                Byte isSign = conventionerAttendRecord.getIsSign();
                if (isSign == null) {
                    cell9.setCellValue("");
                } else {
                    cell9.setCellValue(this.switchUtil(isSign));
                }
                //签到时间
                Cell cell10 = row.createCell(9, HSSFCell.CELL_TYPE_STRING);
                Date signInTime = conventionerAttendRecord.getSignInTime();
                String signInTimeStr = "";
                if (signInTime == null) {
                    cell10.setCellValue("");
                } else {
                    signInTimeStr = sdf.format(signInTime);
                }
                cell10.setCellValue(signInTimeStr);

                //是否支付
                Cell cell11 = row.createCell(10, HSSFCell.CELL_TYPE_STRING);
                Byte isPay = conventionerAttendRecord.getIsPay();
                if (isPay == null) {
                    cell11.setCellValue("");
                } else {
                    cell11.setCellValue(this.switchUtil(isPay));
                }

                //支付账号；
                Cell cell12 = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
                cell12.setCellValue(conventionerAttendRecord.getPayAccount());

                //支付金额
                Cell cell13 = row.createCell(12, HSSFCell.CELL_TYPE_STRING);
                cell13.setCellValue(conventionerAttendRecord.getAmountOfPay());

                //是否通过审核
                Cell cell14 = row.createCell(13, HSSFCell.CELL_TYPE_STRING);
                Byte isApproved = conventionerAttendRecord.getIsApproved();
                if (isApproved == null) {
                    cell14.setCellValue("");
                } else {
                    cell14.setCellValue(this.switchUtil(isApproved));
                }

                //审核时间
                Cell cell15 = row.createCell(14, HSSFCell.CELL_TYPE_STRING);
                Date approvedTime = conventionerAttendRecord.getApprovedTime();
                String approvedTimeStr = "";
                if (approvedTime == null) {
                    cell15.setCellValue("");
                } else {
                    approvedTimeStr = sdf.format(approvedTime);
                }
                cell15.setCellValue(approvedTimeStr);
            }

            //导出Excel文件
            if (sxssfWorkbook != null) {
                try {
                    // excel 表文件名
                    String fileName = title + ".csv";
                    fileName = "attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1");
                    response.setContentType("application/vnd.ms-excel;charset=utf-8");
                    response.setHeader("Content-Disposition", fileName);
                    OutputStream out = response.getOutputStream();
                    sxssfWorkbook.write(out);
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 用于对是否进行判断的小方法
     *
     * @return
     */
    public String switchUtil(Byte isByte) {
        String result = "";
        switch (isByte) {
            case 0:
                result = "否";
                break;
            case 1:
                result = "是";
                break;
            default:
                result = "";
                break;
        }
        return result;
    }


    public CellStyle getColumnTopStyle(SXSSFWorkbook workbook) {

        // 设置字体
        Font font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        CellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;

    }

    public CellStyle getStyle(SXSSFWorkbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        //设置字体大小
        //font.setFontHeightInPoints((short)10);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        CellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;
    }
}